﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_vacation_personal')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_vacation_personal

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-30 16:31:40
-- Database:    [scm_main-hysj2]
-- Description: 假期-个人假期额度查询
-- =============================================

CREATE PROCEDURE bd_proc_vacation_personal
  @userSerial BIGINT
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(20),
    user_name VARCHAR(100),
    dep_id BIGINT,
    dep_name VARCHAR(200),
    balance_ordinary INT DEFAULT 0,    --存工-平时加班
    balance_weekend INT DEFAULT 0,      --存工-双休加班
    balance_owe INT DEFAULT 0,          --欠工
    balance_annual_remain INT DEFAULT 0,    --年假剩余
    balance_annual_usage INT DEFAULT 0,    --年假已使用
    balance_annual_total INT DEFAULT 0,    --年假总额

    balance_annual_usage_tmp  INT,
    balance_annual_usage_cancel INT,

    balance_lactational INT DEFAULT 0,    --授乳假
    balance_day_off_remain INT DEFAULT 0,    --调休剩余
    balance_day_off_usage INT DEFAULT 0,    --调休已使用
    balance_day_off_total INT DEFAULT 0,     --调休总额

    balance_ordinary_hour DECIMAL(8,2) DEFAULT 0,    --存工-平时加班
    balance_weekend_hour DECIMAL(8,2) DEFAULT 0,      --存工-双休加班
    balance_ot_total  DECIMAL(8,2)  DEFAULT 0,        --平时加班+双休加班

    balance_owe_hour DECIMAL(8,2) DEFAULT 0,          --欠工
    balance_annual_remain_hour DECIMAL(8,2) DEFAULT 0,    --年假剩余
    balance_annual_usage_hour DECIMAL(8,2) DEFAULT 0,    --年假已使用
    balance_annual_total_hour DECIMAL(8,2) DEFAULT 0,    --年假总额

    balance_lactational_hour DECIMAL(8,2) DEFAULT 0,    --授乳假
    balance_day_off_remain_hour DECIMAL(8,2) DEFAULT 0,    --调休剩余
    balance_day_off_usage_hour DECIMAL(8,2) DEFAULT 0,    --调休已使用
    balance_day_off_total_hour DECIMAL(8,2) DEFAULT 0     --调休总额
  )


  INSERT INTO #result(user_serial, user_no, user_name, dep_id, dep_name)
  SELECT user_serial, user_no, user_lname, user_dep, dep_name
  FROM bd_view_user_all
  WHERE user_serial = @userSerial

  UPDATE r
  SET balance_ordinary = ht.save_ordinary
     ,balance_weekend = ht.save_weekend
     ,balance_owe = ht.owe
     ,balance_annual_remain = ht.vacation_annual
     ,balance_lactational = ht.vacation_lactational
     ,balance_day_off_remain = ht.rest_total
     ,balance_day_off_total = ht.save_ordinary + ht.save_weekend
     ,balance_day_off_usage = (ht.save_ordinary + ht.save_weekend) - ht.rest_total
  FROM #result r
  INNER JOIN (
      SELECT
        *
      FROM bd_view_kq_holidays_time_rowno
      WHERE user_serial = @userSerial AND row_no = 1
    ) ht ON ht.user_serial = r.user_serial

  --2：年假录入增加
  --5：每年年初初始化
  --8：年假初始化(系统上线初始化)
  UPDATE r
  SET balance_annual_total = ht.deduct_usage
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, SUM(deduct_usage) deduct_usage
      FROM bd_kq_holidays
      WHERE user_serial = @userSerial AND ho_type IN (2, 5, 8)
      GROUP BY user_serial
    ) ht ON ht.user_serial = r.user_serial

      --1：年假申请
      --3：年假录入减少

  UPDATE r
  SET balance_annual_usage_tmp = ht.deduct_usage
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, SUM(deduct_usage) deduct_usage
      FROM bd_kq_holidays
      WHERE user_serial = @userSerial AND ho_type IN (1, 3)
      GROUP BY user_serial
    ) ht ON ht.user_serial = r.user_serial

      --4：年假取消
  UPDATE r
  SET balance_annual_usage_cancel = ht.deduct_usage
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, SUM(deduct_usage) deduct_usage
      FROM bd_kq_holidays
      WHERE user_serial = @userSerial AND ho_type = 4
      GROUP BY user_serial
    ) ht ON ht.user_serial = r.user_serial

  UPDATE #result SET balance_annual_usage = ISNULL(balance_annual_usage_tmp, 0) - ISNULL(balance_annual_usage_cancel, 0)

  UPDATE #result
  SET balance_ordinary_hour = CAST(balance_ordinary / 60.0 AS DECIMAL(8, 2))
     ,balance_weekend_hour = CAST(balance_weekend / 60.0 AS DECIMAL(8, 2))
     ,balance_owe_hour = CAST(balance_owe / 60.0 AS DECIMAL(8, 2))
     ,balance_annual_remain_hour = CAST(balance_annual_remain / 60.0 AS DECIMAL(8, 2))
     ,balance_annual_usage_hour = CAST(balance_annual_usage / 60.0 AS DECIMAL(8, 2))
     ,balance_annual_total_hour = CAST(balance_annual_total / 60.0 AS DECIMAL(8, 2))
     ,balance_lactational_hour = CAST(balance_lactational / 60.0 AS DECIMAL(8, 2))
     ,balance_day_off_remain_hour = CAST(balance_day_off_remain / 60.0 AS DECIMAL(8, 2))
     ,balance_day_off_usage_hour = CAST(balance_day_off_usage / 60.0 AS DECIMAL(8, 2))
     ,balance_day_off_total_hour = CAST(balance_day_off_total / 60.0 AS DECIMAL(8, 2))
     ,balance_ot_total = CAST((balance_ordinary + balance_weekend) / 60.0 AS DECIMAL(8, 2))

  SELECT * FROM #result

  DROP TABLE #result

  SET NOCOUNT OFF;
END